package cc.inq.db.tools;

import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Observable;
import java.util.Observer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableColumn;
import org.eclipse.swt.widgets.TableItem;
import org.eclipse.swt.widgets.Text;
import org.firefly.db.dialect.IDialect;
import org.firefly.db.dialect.MSSQL2005Dialect;
import org.firefly.db.dialect.MySQLDialect;
import org.firefly.db.dialect.OracleDialect;
import org.firefly.db.util.DBInfo;
import org.firefly.db.util.DBName;
import org.firefly.db.util.DBUtils;

public class Main extends Observable implements IWin {
	Shell shell;
	Display display;
	DBInfo srcInfo;
	int count = 0;
	Table t;
	Text sqlTxt;
	Text tableNameTxt;
	Label lab = null; // 显示查询统计信息
	
	/**
	 * Create the composite.
	 * 
	 * @param shell
	 * @param style
	 */
	public Main(DBInfo dbInfo, Observer obServer) {
		this.srcInfo = dbInfo;
		this.addObserver(obServer);
		display = new Display();
		shell = new Shell(display, SWT.CLOSE | SWT.MIN);
		shell.setText(Const.WIN_TITLE);
		GridLayout gridLayout = new GridLayout();
		gridLayout.numColumns = 5;
		gridLayout.marginTop = gridLayout.marginRight = gridLayout.marginBottom = gridLayout.marginLeft = 20;
		shell.setLayout(gridLayout);

		addLabel("当前库:" + getDBName() + "[" + getVersion() + "]", 200, 2);
		addInput("URL", srcInfo.getUrl(), 350, 3, true);

		addLabel("SQL", 100, 4);
		addBtn("查询", 100, 25, 1, new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				String sql = sqlTxt.getText();
				if (checkSQL(sql)) {
					makeTableName(sql);
					Statement s = null;
					try {
						s = srcInfo.getConn().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // 默认指针只能向前滚动，通过设置调整为可调整 ，否则会报如下错误：ResultSet may only be accessed in a forward direction 
						ResultSet rs = s.executeQuery(sql.trim());
						addTableContent(rs);
						rs.last();
						count = rs.getRow();
						lab.setText("数据内容（总记录数：" + count + ")，最多展示10行");
					} catch (SQLException e) {
						WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "sql错误", e.getMessage());
						return;
					} finally {
						if (s != null) {
							try {
								s.close();
							} catch (SQLException e) {
							}
						}
					}
				}
			}

		});
		sqlTxt = addText(600, 200, 5);
		sqlTxt.setFocus();
		lab = addLabel("数据内容（总记录数：" + count + ")，最多展示10行", 600, 5);
		
		t = addTable(600, 200, 5);

		final Combo cb = addSelect("导出类型", new String[] { Const.DB_MSSQL, Const.DB_MYSQL, Const.DB_ORACLE },
				getDBName(), 100, 2);
		tableNameTxt = addInput("表名", null, 100, 2, false);
		addBtn("导出sql", 100, 25, 1, new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				String sql = sqlTxt.getText();
				if (checkSQL(sql)) {
					String tableName = tableNameTxt.getText();
					if (tableName == null && tableName.trim().length() <1) {
						WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "无法导出文件", "请为导出数据提供表名");
						return;
					}
					IDialect dialect = getIDialect(cb.getText());
					if (dialect == null) {
						WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "无法导出文件", "请选择导出数据库类型");
						return;
					}
					Statement s = null;
					try {
						s = srcInfo.getConn().createStatement();
						ResultSet rs = s.executeQuery(sql.trim());
						List<String> insertSQLs = DBUtils.buildInsertSQL(tableName, rs, dialect);
						OutputStreamWriter osw = WinUtil.makeFile("数据导出.sql");
						if (osw != null) {
							osw.write("\n\n--表["+tableName+"]的数据如下:\n");
							for (int j = 0; j < insertSQLs.size(); j++) {
								osw.write(insertSQLs.get(j)+";\n");
							}
							osw.flush();
							osw.close();
						}
					} catch (SQLException e) {
						WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "sql错误", e.getMessage());
					} catch (IOException e) {
						WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "导出错误", e.getMessage());
					} finally {
						if (s != null) {
							try {
								s.close();
							} catch (SQLException e) {
							}
						}
					}

				}
			}

		});
	}

	/**
	 * 检查sql
	 * 
	 * @author firefly
	 * @scene 2019年3月1日
	 * @param sql
	 * @return
	 */
	private boolean checkSQL(String sql) {
		if (sql != null) {
			sql = sql.trim().toLowerCase();
			if (!sql.startsWith("select")) {
				WinUtil.showMsg(shell, SWT.OK | SWT.ICON_WARNING, "sql错误", "只支持select查询");
				return false;
			} else {
				return true;
			}
		}
		return false;
	}
	
	/**
	 * 根据sql生成导出表名
	 * @author firefly
	 * @scene 2019年3月1日
	 * @param sql
	 */
	private void makeTableName(String sql) {
		Pattern pattern = Pattern.compile("from\\s([^,|\\s]+)");
		Matcher m = pattern.matcher(sql);
		if(m.find()) {
			tableNameTxt.setText(m.group(1));
		}
	}

	public IDialect getIDialect(String dbType) {
		return Const.DB_MYSQL.equals(dbType) ? new MySQLDialect() : Const.DB_MSSQL.equals(dbType) ? new MSSQL2005Dialect() : Const.DB_ORACLE.equals(dbType) ? new OracleDialect() : null ;
	}
	
	public void open() {
		shell.setSize(400, 200);
		shell.pack();
		shell.open();
		WinUtil.center(display, shell);
		while (!shell.isDisposed()) {
			if (!display.readAndDispatch())
				display.sleep();
		}
	}

	public void close() {
		this.shell.dispose();
		try {
			srcInfo.getConn().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		display.dispose();
	}

	public String getDBName() {
		return isMysql() ? Const.DB_MYSQL
				: isMsSql() ? Const.DB_MSSQL
						: isOracle() ? Const.DB_ORACLE : "未知";
	}
	
	private boolean isMysql() {
		return this.srcInfo.getDbName() == DBName.Mysql;
	}

	private boolean isOracle() {
		return this.srcInfo.getDbName() == DBName.Oracle;
	}
	
	private boolean isMsSql() {
		return this.srcInfo.getDbName() == DBName.SqlServer2005;
	}

	public String getVersion() {
		Statement s = null;
		try {
			s = srcInfo.getConn().createStatement();
			String sql  = null;
			if (isMsSql()) {
				sql = "SELECT @@VERSION";
			} else if (isMysql()) {
				sql = "SELECT VERSION()";
			} else if (isOracle()) {
				sql = "select VERSION from product_component_version";
			}
			if (sql != null) {				
				ResultSet rs = s.executeQuery(sql);
				if (rs.next()) {
					String version = rs.getString(1); // mysql
					if(isMsSql() && version.startsWith("Microsoft SQL Server")) {
						version = version.substring("Microsoft SQL Server".length(), version.indexOf("\n"));
					}
					return version;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (s != null) {
				try {
					s.close();
				} catch (SQLException e) {
				}
			}
		}
		return "";
	}

	/**
	 * 生成文本框
	 * 
	 * @author firefly
	 * @scene 2019年2月27日
	 * @param label
	 *            标签
	 * @param defVal
	 *            默认值
	 * @param width
	 *            宽度
	 * @param hIndent
	 *            横向占位
	 */
	public Text addInput(String label, String defVal, int width, int hIndent, boolean readOnly) {
		if (label != null) {
			new Label(this.shell, SWT.NONE).setText(label);
			hIndent--;
		}
		int type = SWT.BORDER;
		if (readOnly) {
			type = type | SWT.READ_ONLY;
		}
		Text txt = new Text(this.shell, type);
		if (defVal != null) {
			txt.setText(defVal);
		}
		// GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL, SWT.NONE, false, false, hIndent, 1);
		// gd.horizontalAlignment = GridData.FILL_HORIZONTAL;
		// gd.horizontalSpan = hIndent;
		// gd.horizontalIndent = hIndent;
		gd.widthHint = width;
		// gridData.heightHint = 80;
		txt.setLayoutData(gd);
		return txt;
	}

	public Label addLabel(String label, int width, int hIndent) {
		Label lab = new Label(this.shell, SWT.NONE);
		lab.setText(label);
		GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		gd.horizontalAlignment = GridData.FILL_HORIZONTAL;
		gd.horizontalSpan = hIndent;
		gd.widthHint = width;
		lab.setLayoutData(gd);
		return lab;
	}

	/**
	 * 生成文本框
	 * 
	 * @author firefly
	 * @scene 2019年2月27日
	 * @param label
	 *            标签
	 * @param defVal
	 *            默认值
	 * @param width
	 *            宽度
	 * @param hIndent
	 *            横向占位
	 */
	public Combo addSelect(String label, String[] options, String defVal, int width, int hIndent) {
		if (label != null) {
			new Label(this.shell, SWT.NONE).setText(label);
			hIndent--;
		}
		Combo cb = new Combo(this.shell, SWT.DROP_DOWN | SWT.READ_ONLY);
		cb.setItems(options);
		if (defVal != null) {
			cb.setText(defVal);
		}
		GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		gd.horizontalAlignment = GridData.FILL_HORIZONTAL;
		gd.horizontalSpan = hIndent;
		gd.widthHint = width;
		cb.setLayoutData(gd);
		return cb;
	}

	public Text addText(int width, int height, int hIndent) {
		Text text = new Text(shell, SWT.MULTI | SWT.BORDER | SWT.V_SCROLL | SWT.H_SCROLL);
		GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		gd.horizontalAlignment = GridData.FILL_HORIZONTAL;
		gd.horizontalSpan = hIndent;
		gd.widthHint = width;
		gd.heightHint = height;
		// FormData formData3 = new FormData();
		// formData3.top = new FormAttachment(0, 5);
		// formData3.bottom = new FormAttachment(0, -5);// 底部距离 【底部控件-5个像素】
		// formData3.left = new FormAttachment(0, 5);
		// formData3.right = new FormAttachment(100, -5);
		text.setLayoutData(gd);
		return text;
	}

	/**
	 * 生成按钮
	 * 
	 * @author firefly
	 * @scene 2019年2月27日
	 * @param label
	 *            标签
	 * @param width
	 *            宽度
	 * @param hIndent
	 *            横向占位
	 */
	public void addBtn(String label, int width, int height, int hIndent, SelectionAdapter click) {
		Button enter = new Button(this.shell, SWT.PUSH);
		enter.setText(label);
		GridData gridData = new GridData(GridData.HORIZONTAL_ALIGN_CENTER);
		gridData.horizontalSpan = hIndent;
		gridData.widthHint = width;
		gridData.heightHint = height;
		enter.setLayoutData(gridData);
		enter.addSelectionListener(click);
	}

	public Table addTable(int width, int height, int hIndent) {
//		Composite composite_1 = new Composite(shell, SWT.H_SCROLL | SWT.V_SCROLL);
		GridData gd = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		gd.horizontalAlignment = GridData.FILL_HORIZONTAL;
		gd.horizontalSpan = hIndent;
		gd.widthHint = width;
		gd.heightHint = height;
//		composite_1.setLayout(new FillLayout(SWT.HORIZONTAL));

		Table tb = new Table(shell, SWT.BORDER | SWT.FULL_SELECTION);
		tb.setHeaderVisible(true);
		tb.setLinesVisible(true);
		tb.setLayoutData(gd);
		return tb;
	}

	/**
	 * 生成最多10行查询结果
	 * @author firefly
	 * @scene 2019年3月1日
	 * @param rs
	 * @throws SQLException
	 */
	public void addTableContent(ResultSet rs) throws SQLException {
		ResultSetMetaData rsm = rs.getMetaData();
		t.removeAll();
		for (TableColumn c : t.getColumns()) {
			c.dispose();
		}
		// 表头
		for (int i = 1; i <= rsm.getColumnCount(); i++) {
			TableColumn tableColumn = new TableColumn(t, SWT.NONE);
			tableColumn.setText(rsm.getColumnLabel(i));
			// 设置表头可移动，默认为false
			tableColumn.setMoveable(true);
		}
		// 内容
		int k = 0;
		while (rs.next() && k < 10) {
			TableItem row = new TableItem(t, SWT.NONE);
			String[] rowData = new String[rsm.getColumnCount()];
			for (int i = 0; i < rsm.getColumnCount(); i++) {
				Object v = rs.getObject(i + 1);
				rowData[i] = v == null ? null : v.toString();//srcInfo.getDialect().processSQLVal(v, rsm.getColumnType(i + 1), null);
			}
			row.setText(rowData);
			k ++;
		}

		t.layout();
		// 重新布局表格
		for (int i = 0; i < rsm.getColumnCount(); i++) {
			t.getColumn(i).pack();
		}
	}
}
